﻿//----------------------------------------------------------------------------
// Copyright (c) Pavel Tishkin 2012-2013.
//
// You must not remove this notice, or any other, from this software.
//----------------------------------------------------------------------------

namespace Lib.Dxp.Orm

open System.Collections
open System.Reflection
open System.Data
open Lib.Dxp.Core
open Lib.Dxp.Orm.Dsl


module private ExtTablePrivate =
    /// ValueO, ValueT, ObjectID, FieldName
    let qry_UpdateProperty2008 tablename = sprintf @"MERGE %s_Ext AS target
USING (VALUES (@a0, @a1))
    AS source (field1, field2)
    ON target.ObjectID = @a2 AND target.FieldName = @a3
WHEN matched THEN
    UPDATE SET valueO = source.field1, valueT = source.field2
WHEN NOT matched THEN
    INSERT ( valueO, valueT, ObjectID, FieldName )
    VALUES ( source.field1, source.field2, @a2, @a3 );" tablename

    let qry_UpdateProperty tablename = sprintf @"
    DELETE FROM %s_Ext WHERE ObjectID = @a2 AND FieldName = @a3;
    INSERT INTO %s_Ext ( valueO, valueT, ObjectID, FieldName )
    VALUES ( @a0, @a1, @a2, @a3 );"         tablename tablename


/// Has Document storage as record in table "baseTable_Ext" ( "ObjectID", "FieldName", "ValueO" AS SqlVariant, "ValueT" AS Text )
///       * DB must contains ("ObjectID", "FieldName") is unique index in "baseTable_Ext"
///       * DB must contains "update_baseTable_Ext( "ObjectID", "FieldName", "ValueO" AS SqlVariant, "ValueT" AS Text )" STOREDPROC 
///         for writing information into "baseTable_Ext"
type ExtTable  = 
    inherit DocumentTable 

    new () = { inherit DocumentTable() }
    //new (session) = { inherit DocumentTable(session) }

    override x.save   () = // current or generated Id
        let isChangedS, dictS    = x.get_staticSetFieldsAsDict(onlyChanged = true)
        let isChangedD, dictD    = x.get_dynamicValuesAsDict(onlyChanged = true)
        if isChangedS || isChangedD then
            //printfn ":: %A" x.Id.Value
            match x.Id.Value with
            | 0     -> x.inserter isChangedS isChangedD dictS dictD
            | _     -> x.updater  isChangedS isChangedD dictS dictD
        else
            0        


    member x.inserter isChangedS isChangedD staticDict dynamicDict = 
        use q = cmd_insert [ update (seq staticDict.Values) ]
        let session = x.SessionBegin()
        let id = session.insert q 
        if isChangedD then
            x.updateDynamic dynamicDict session id
        x.SessionEnd()
        x.Id.Load <- id
        //printfn ":: %A" (id, x.Id.Value)
        x.Id.Value

    

    member x.updater isChangedS isChangedD staticDict dynamicDict = 
        //Log.Debug(sprintf "~ udss %A" <| staticDict.Values)
        let session = x.SessionBegin()
        if isChangedS then
            use q = cmd_update [ update (seq staticDict.Values)
                                 where (x.Id ==. x.Id.Value) ]
            session.exec q |> ignore
        if isChangedD then
            x.updateDynamic dynamicDict session x.Id.Value
        x.SessionEnd()
        x.Id.Value 

    (*
    #r @"C:\Proj\2012\SCIAS\packages\ServiceStack.Text.3.9.49\lib\sl4\ServiceStack.Text.dll";;
    *)

    // String always in ValueT
    member x.updateDynamic dynamicDict session idVal = 
        let xs : _ seq = seq dynamicDict
        use cmd = new SqlClient.SqlCommand(ExtTablePrivate.qry_UpdateProperty x._MetaName)
        //Log.Debug ("DYNO ", cmd.CommandText)
        xs |> iter (function KeyValue(fname, e) -> 
                                let dbNull = System.DBNull.Value
                                let cmd' = match e with
                                           | :? string -> //when (to_s e).Length > 250  -> 
                                                session.Provider.MapParams(cmd, [| dbNull; e; idVal; fname |])
                                           | :? int 
                                           | :? System.DateTime
                                           | :? double
                                           | :? decimal  -> 
                                                session.Provider.MapParams(cmd, [| e; dbNull; idVal; fname |])
                                           | _  -> 
                                                let vl = ServiceStack.Text.JsonSerializer.SerializeToString(e) 
                                                session.Provider.MapParams(cmd, [| dbNull; vl; idVal; fname |])
                                session.exec cmd' |> ignore
                                )


    member x.find(cond : MetaFieldCond) = 
        let q = cmd_select [ select [ x.Id ] 
                             where cond ]
        let session = x.SessionBegin()
        let result = session.dataset q |> map (fun e -> e.[0]) |> toList
        x.SessionEnd()
        result


    member x.loadWhere(cond : MetaFieldCond) = 
        match x.find cond with
        | id :: _ -> let id : int = unbox id in x.load id
        | _ -> ()


    /// Check id > 0 then load(id)
    //     !-- cannot return `x` due different type or return value
    member x.loadById id = 
        if id > 0 then x.load id else ()


    /// Load object fields from Database
    override x.load   id  = 
        let _, dict'    = x.get_staticValuesAsDict()
        let fields      = dict'.Keys |> Seq.toArray
        let fieldsPart  = System.String.Join(", ", fields)

        let session = x.SessionBegin()
        use cmd = session.Provider.MapParams(
                        session.Command(sprintf "SELECT %s FROM %s WHERE ID = @a0" fieldsPart x._MetaName), [| id |])
        let rows = session.rowset cmd
        let dictS = Seq.map2 (fun a b -> a,b) fields rows |> dict

        use cmd = session.Provider.MapParams(
                        session.Command(sprintf "SELECT FieldName, ValueO, ValueT FROM %s_Ext WHERE ObjectID = @a0" x._MetaName), [| id |])
        let _, template'    = x.get_dynamicValuesAsDict()
        
        let dictD = System.Collections.Generic.Dictionary<string, obj>() 
        /// TODO convert types
        for xs in session.dataset cmd do
            match xs with 
            | [| fieldname'; valueo; valuet |] -> 
                    let fieldname = unbox fieldname'
                    dictD.Add( fieldname, if isSome valueo then valueo else valuet )
            | _ -> ()
        x.load_AnyFromDict dictD
        x.load_AnyFromDict dictS

        x.SessionEnd()


    /// Delete object in Database
    override x.delete () = 
        let session = x.SessionBegin()

        use cmd = session.Provider.MapParams(
                        session.Command(sprintf "DELETE FROM %s_Ext WHERE ObjectID = @a0" x._MetaName) 
                        , [| x.Id.Value |])
        session.exec cmd |> ignore
        
        use cmd = session.Provider.MapParams(
                        session.Command(sprintf "DELETE FROM %s WHERE ID = @a0" x._MetaName) 
                        , [| x.Id.Value |])
        session.exec cmd |> ignore

        x.Id.Value <- 0
        // x.reset()    unnecessary ???

        x.SessionEnd()

